package com.example.wenhua.service.impl;

import com.example.wenhua.dto.AiQueryResponse;
import com.example.wenhua.service.QueryExecutorService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 查询执行服务实现类
 */
@Slf4j
@Service
public class QueryExecutorServiceImpl implements QueryExecutorService {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Override
    public List<Map<String, Object>> executeQuery(String sql) {
        try {
            log.info("执行SQL查询: {}", sql);
            long startTime = System.currentTimeMillis();
            
            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
            
            long endTime = System.currentTimeMillis();
            log.info("SQL查询完成，耗时: {}ms，结果数量: {}", (endTime - startTime), result.size());
            
            return result;
            
        } catch (Exception e) {
            log.error("执行SQL查询失败: {}", e.getMessage(), e);
            throw new RuntimeException("查询执行失败: " + e.getMessage());
        }
    }
    
    @Override
    public AiQueryResponse executeQueryWithResponse(String sql, String originalQuery, boolean needExplanation) {
        AiQueryResponse response = new AiQueryResponse();
        long startTime = System.currentTimeMillis();
        
        try {
            // 执行查询
            List<Map<String, Object>> data = executeQuery(sql);
            
            // 获取列信息
            List<AiQueryResponse.ColumnInfo> columns = getColumnInfo(sql);
            
            // 计算执行时间
            long executionTime = System.currentTimeMillis() - startTime;
            
            // 构建响应
            response.setSuccess(true);
            response.setData(data);
            response.setTotalCount(data.size());
            response.setColumns(columns);
            response.setGeneratedSql(sql);
            response.setExecutionTime(executionTime);
            
            // 生成解释（如果需要）
            if (needExplanation) {
                String explanation = generateQueryExplanation(sql, originalQuery, data.size());
                response.setExplanation(explanation);
            }
            
            log.info("查询执行成功，返回 {} 条记录", data.size());
            
        } catch (Exception e) {
            log.error("查询执行失败: {}", e.getMessage(), e);
            response.setSuccess(false);
            response.setErrorMessage(e.getMessage());
            response.setExecutionTime(System.currentTimeMillis() - startTime);
        }
        
        return response;
    }
    
    @Override
    public List<AiQueryResponse.ColumnInfo> getColumnInfo(String sql) {
        List<AiQueryResponse.ColumnInfo> columns = new ArrayList<>();
        
        try {
            log.info("开始获取列信息，SQL: {}", sql);
            
            // 方法1：使用PreparedStatement获取元数据
            try {
                jdbcTemplate.execute(sql, (java.sql.PreparedStatement ps) -> {
                    ResultSetMetaData metaData = ps.getMetaData();
                    int columnCount = metaData.getColumnCount();
                    log.info("通过PreparedStatement获取到列数量: {}", columnCount);
                    
                    for (int i = 1; i <= columnCount; i++) {
                        AiQueryResponse.ColumnInfo columnInfo = new AiQueryResponse.ColumnInfo();
                        String columnName = metaData.getColumnName(i);
                        String columnType = metaData.getColumnTypeName(i);
                        String columnLabel = getColumnLabel(columnName);
                        
                        columnInfo.setName(columnName);
                        columnInfo.setType(columnType);
                        columnInfo.setLabel(columnLabel);
                        columns.add(columnInfo);
                        
                        log.info("添加列信息: name={}, type={}, label={}", columnName, columnType, columnLabel);
                    }
                    return null;
                });
            } catch (Exception e1) {
                log.warn("PreparedStatement方法失败，尝试LIMIT 1方法: {}", e1.getMessage());
                
                // 方法2：使用LIMIT 1获取一行数据来获取列信息
                String limitSql = sql + " LIMIT 1";
                log.info("执行的限制SQL: {}", limitSql);
                
                jdbcTemplate.query(limitSql, (rs) -> {
                    try {
                        ResultSetMetaData metaData = rs.getMetaData();
                        int columnCount = metaData.getColumnCount();
                        log.info("通过LIMIT 1获取到列数量: {}", columnCount);
                        
                        for (int i = 1; i <= columnCount; i++) {
                            AiQueryResponse.ColumnInfo columnInfo = new AiQueryResponse.ColumnInfo();
                            String columnName = metaData.getColumnName(i);
                            String columnType = metaData.getColumnTypeName(i);
                            String columnLabel = getColumnLabel(columnName);
                            
                            columnInfo.setName(columnName);
                            columnInfo.setType(columnType);
                            columnInfo.setLabel(columnLabel);
                            columns.add(columnInfo);
                            
                            log.info("添加列信息: name={}, type={}, label={}", columnName, columnType, columnLabel);
                        }
                    } catch (Exception e) {
                        log.error("处理列元数据失败: {}", e.getMessage(), e);
                    }
                });
            }
            
            log.info("最终获取到的列信息数量: {}", columns.size());
            
        } catch (Exception e) {
            log.error("获取列信息失败: {}", e.getMessage(), e);
        }
        
        return columns;
    }
    
    @Override
    public String generateQueryExplanation(String sql, String originalQuery, int resultCount) {
        StringBuilder explanation = new StringBuilder();
        
        explanation.append("查询解释：\n");
        explanation.append("原始查询：").append(originalQuery).append("\n");
        explanation.append("生成的SQL：").append(sql).append("\n");
        explanation.append("查询结果：共找到 ").append(resultCount).append(" 条记录\n");
        
        // 根据SQL内容生成更详细的解释
        String upperSql = sql.toUpperCase();
        
        if (upperSql.contains("JOIN")) {
            explanation.append("该查询涉及多表关联查询\n");
        }
        
        if (upperSql.contains("WHERE")) {
            explanation.append("查询包含筛选条件\n");
        }
        
        if (upperSql.contains("GROUP BY")) {
            explanation.append("查询包含分组统计\n");
        }
        
        if (upperSql.contains("ORDER BY")) {
            explanation.append("查询结果已排序\n");
        }
        
        if (resultCount == 0) {
            explanation.append("提示：未找到符合条件的记录，请检查查询条件是否正确\n");
        } else if (resultCount > 100) {
            explanation.append("提示：查询结果较多，建议添加更具体的筛选条件\n");
        }
        
        return explanation.toString();
    }
    
    /**
     * 获取列的中文标签
     */
    private String getColumnLabel(String columnName) {
        switch (columnName.toLowerCase()) {
            case "sno": return "学号";
            case "sname": return "姓名";
            case "sex": return "性别";
            case "age": return "年龄";
            case "dept": return "系名/班级";
            case "cno": return "课程号";
            case "cname": return "课程名";
            case "teacher": return "教师";
            case "credit": return "学分";
            case "grade": return "成绩";
            case "hm": return "用户名";
            case "role": return "角色";
            case "total_credits": return "总学分";
            case "avg_grade": return "平均成绩";
            case "count": return "数量";
            default: return columnName;
        }
    }
}